<?php

defined('_SECURITY_TOKEN') or die("caia fora");

class modelextraction extends model
{
	
	function __construct(  )
	{
		
		
		
	}
	
	
	public function getViewDataDb( $user_id, $parameters ){
				
		$result=false;
		try {
			
				
					
			$db = application::getDb( configuration::db_type);
	// 		
			$db->setParameters(configuration::db_server, 
								configuration::db_port, 
								configuration::db_username, 
								configuration::db_password,
								configuration::db_name);
	
			$id	=	$parameters->id;
			
			if( !empty($id)){
			
				$query = sprintf("SELECT * FROM \"%s%s\" 
								WHERE user_id=%d and id=%d;",
									configuration::table_prefix,
				                 	configuration::table_view, 
				                 	application::getUser_id(), 
				                 	$id);
						
				$result = $db->getQuery($query);
				
				
				if(count($result)>0){
					
					
					if( $this->exists_table(md5($id))){
					
						$query = sprintf("SELECT * FROM \"%s\" LIMIT 50 offset 0",
										md5($id));
							
						$result = $db->getQuery($query);
					//var_dump($query);
					//exit("bruno");
						if(count($result)>0){
							
																	
						}else{
							$result=false;
						}
						
					}else{
						$result = false;//array(array("status"=>"extraction run first"));
					}
					
				}
				
			}
		
	
		 }
	    catch (AppException $e) {
	    	throw new AppException( $e->getMessage() );
	    }
	
	

		return $result;
		
	}

	


	public function viewExecute( $user_id, $parameters ){
				
		$result=false;
		
		try {
					
			$db = application::getDb( configuration::db_type);
	// 		
			$db->setParameters(configuration::db_server, 
								configuration::db_port, 
								configuration::db_username, 
								configuration::db_password,
								configuration::db_name);
	
			$id	=	$parameters->id;
			
			if( !empty($id)){
			
				$query = sprintf("SELECT * FROM \"%s%s\" 
									WHERE user_id=%d and id=%d;",
									configuration::table_prefix,
				                 	configuration::table_view, 
				                 	application::getUser_id(), 
				                 	$id);
						
				$result = $db->getQuery($query);
				
				
				
				if(count($result)>0){
					
					//var_dump($result);
				
					try{
						
						$db2 = application::getDb( $result[0]->db_type);
						
						$db2->setParameters($result[0]->db_hostip, 
											$result[0]->db_port,
											$result[0]->db_user,
											$result[0]->db_password,
											$result[0]->db_name);
			
						$result = $db2->getQuery($result[0]->db_query);
					
					}catch (AppException $e) {
	    	
						return false;
						//if( $e->getMessage() == "SQLSTATE[08006] [7] timeout expired"){
						//	throw new AppException( MSG_17_ERRO_AO_CONECTAR_SGDB );
						//}else{
						//	throw new AppException( $e->getMessage() );
						///}
				    	
	   				}
					
				//exit("iran");
				//var_dump( $result );exit("maciel");
				
					//if(count($result)>0){
						
					if( $result ){
						// //var_dump( $result );	
						
						if( $this->exists_table( md5($id) )){
							//ja existe
							$this->drop_table($id);
						}
						
						
						//não existe a tabela
						$query_table = $this->generate_create_table_view($id,
																	$result);
						
						$this->create_table_data_view($query_table);
						
						
						$dados = $this->load_data_to_table_view( md5($id), 
																	$result );
						
						
						$this->update_view_table_name(application::getUser_id(), 
																		$id);
						
						
						//for( $z=50; $z <count($result);$z++ ){
						//	unset($result[$z]);
						//}
						//echo $dados;
						//exit("bruno");
					
		
						
						//$this->detect_types_columns($result);
						
						//$this->save_data_view($result);
						
					}else{
						$result=false;
					}
					
				}
			}
		
		}
	    catch (AppException $e) {
	    	
			if( $e->getMessage() == "SQLSTATE[08006] [7] timeout expired"){
				throw new AppException( MSG_17_ERRO_AO_CONECTAR_SGDB );
			}else{
				throw new AppException( $e->getMessage() );
			}
	    	
	    }
	    //catch (Exception $e) {
	     //   echo "#Erro Fatal ('{$e->getMessage()}')\n{$e}\n";
	   // }
				
		//$query = sprintf("SELECT  * FROM %s WHERE user_id=%d;",
        //         	configuration::table_view, application::getUser_id());

		

		return $result;
		
	}
	
	
	public function update_view_table_name( $user_id, $id ){
				
		$result=false;
				
		try {
			
			$columns=array();
        						
			$db = application::getDb( configuration::db_type);
	// 		
			$db->setParameters(configuration::db_server, 
								configuration::db_port, 
								configuration::db_username, 
								configuration::db_password,
								configuration::db_name);
			//
			$query = sprintf("UPDATE \"%s%s\" set 
										table_name='%s',
										last_update_data=statement_timestamp()
							 WHERE 
							 	user_id=%d 
							 	and 
							 	id=%d;",
							configuration::table_prefix, 	
                 			configuration::table_view, 
                 			md5($id), 
                 			application::getUser_id(), 
                 			$id);
			
			try{
				
				$result = $db->setQuery($query);
			}
		    catch (AppException $e) {
		    	throw new AppException( $e->getMessage() );
		    }

	    }
	    catch (AppException $e) {
	    	throw new AppException( $e->getMessage() );
	    }
	
		return $result;
		
	}

	
	
	public function load_data_to_table_view( $table_name, $itens ){
				
		$result=false;
				
		try {
			
			$columns=array();
        						
			$db = application::getDb( configuration::db_type);
	// 		
			$db->setParameters(configuration::db_server, 
								configuration::db_port, 
								configuration::db_username, 
								configuration::db_password,
								configuration::db_name);
			
			$query="";
			
			foreach ($itens as $key=>$value) {
				$i=0;
				
				$query .="INSERT INTO \"".$table_name."\"  ";
				$columns="";
				$valores="";
				
				 foreach ($value as $key2=> $value2) {
					
					if( $i>0){
						$columns .= sprintf(",%s", $key2);
						$valores .= sprintf(",'%s'", $value2);
					}						
					else{
						$columns .= sprintf("%s", $key2);
						$valores .= sprintf("'%s'", $value2);
					}	
						
					
					$i++;
				 }
				 
				 $query .= "(".$columns.") values (".$valores.")";
				 
				try{
					
					$result = $db->setQuery($query);
				}
			    catch (AppException $e) {
			    	throw new AppException( $e->getMessage() );
			    }
			
				 //$result = $db->setQuery($query);
				 $query="";
			}
			
			$result =$query;
			
	    }
	    catch (AppException $e) {
	    	throw new AppException( $e->getMessage() );
	    }
	
		return $result;
		
	}
	
	
	
	public function create_table_data_view( $query ){
				
		$result=false;
				
		try {
        						
			$db = application::getDb( configuration::db_type);
	// 		
			$db->setParameters(configuration::db_server, 
								configuration::db_port, 
								configuration::db_username, 
								configuration::db_password,
								configuration::db_name);
	
			try{
					
				$result = $db->setQuery($query);
			}
		    catch (AppException $e) {
		    	throw new AppException( $e->getMessage() );
		    }

			$result=true;
			
	    }
	    catch (AppException $e) {
	    	throw new AppException( $e->getMessage() );
	    }
	
		return $result;
		
	}
	
	
	public function save_data_view( $itens ){
				
		$result=false;
				
		try {
        						
			$db = application::getDb( configuration::db_type);
	// 		
			$db->setParameters(configuration::db_server, 
								configuration::db_port, 
								configuration::db_username, 
								configuration::db_password,
								configuration::db_name);

			try{
					
				$result = $db->setQuery($query);
			}
		    catch (AppException $e) {
		    	throw new AppException( $e->getMessage() );
		    }
			

			
	    }
	    catch (AppException $e) {
	    	throw new AppException( $e->getMessage() );
	    }
	
		return $result;
		
	}
	
	
	
	public function drop_table( $id ){
				
		$result=false;
				
		try {
        						
			$db = application::getDb( configuration::db_type);
	// 		
			$db->setParameters(configuration::db_server, 
								configuration::db_port, 
								configuration::db_username, 
								configuration::db_password,
								configuration::db_name);
	
			
			$query = sprintf("DROP TABLE \"%s\"",  md5($id) );
		
			try{
					
				$result = $db->setQuery($query);
			}
		    catch (AppException $e) {
		    	throw new AppException( $e->getMessage() );
		    }
			
			
	    }
	    catch (AppException $e) {
	    	throw new AppException( $e->getMessage() );
	    }
	
		return $result;
		
	}
	
	
	public function exists_table( $table_name ){
				
		$result=false;
				
		try {
        						
			$db = application::getDb( configuration::db_type);
	// 		
			$db->setParameters(configuration::db_server, 
								configuration::db_port, 
								configuration::db_username, 
								configuration::db_password,
								configuration::db_name);
	
			
			
			;
			
			$query = sprintf("select count(*) as exists 
								from pg_stat_user_tables 
								WHERE relname='%s';",
                 	 			$table_name);
					
			//var_dump( $query);	
							
			//print( $query );
			$result = $db->getQuery($query);
			
			//var_dump( $result);
			
			if( $result[0]->exists){
				
				$result=TRUE;
			}else{
				$result=FALSE;
			}
			
			//$result=true;
			
	    }
	    catch (AppException $e) {
	    	throw new AppException( $e->getMessage() );
	    }
	
		return $result;
		
	}
	
	
	
	public function generate_create_table_view( $id, $itens ){
				
		$result=false;
				
		try {
			
			$columns=array();
			
			//$types_col = $this->detect_types_columns( $itens );

        	
			$result="CREATE TABLE \"".md5($id)."\"  (";
			
			foreach ($itens as $key => $value) {
				$i=0;
				 foreach ($value as $key2=> $value2) {
						
					if($i>0)	
						$result .= ",".$key2." character varying(900)";
					else
						$result .= $key2." character varying(900)";
					
					$i++;
				 }
				 break;
			}
			
			$result .=")";
			
	    }
	    catch (AppException $e) {
	    	throw new AppException( $e->getMessage() );
	    }
	
		return $result;
		
	}
	
	
	public function detect_types_columns( $itens ){
				
		$result=false;
				
		try {
			
			$columnType="";//array();
        	$columnLength="";
			

								
			foreach ($itens as $key => $value) {
				 foreach ($value as $key2=> $value2) {
				 	$columnType[$key2]='numeric';
					$columnLength[$key2]=0;
				 }
				 break;
			}
			
			//
			
			foreach ($itens as $key=>$value) {
				 foreach ($value as $key2=>$value2) {

					switch( application::getTypeData($value2) ){
//						case 'numeric':
//
//							break;
						case 'string':
							
							$columnType[$key2]='string';
							
							if( strlen($value2) > $columnLength[$key2])
								$columnLength[$key2]=strlen($value2);
						
							break;
					}
					
					
					
				 }
				 break;
			}
			
			
			
			$result = array("column_type"=>$columnType, 
							"column_lenght"=>$columnLength);
			
			
	    }
	    catch (AppException $e) {
	    	throw new AppException( $e->getMessage() );
	    }
	
		return $result;
		
	}
	
	
}